package com.isoft.service.impl;
import com.isoft.pojo.dto.ExportRequestDTO;
import com.isoft.pojo.dto.LifecycleImportDTO;
import com.isoft.pojo.vo.LifecycleRecordVO;
import com.isoft.result.Result;
import com.isoft.service.ReportService;
import com.isoft.service.WorkspaceService;
import com.isoft.mapper.LifecycleRecordMapper;
import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service
public class ResportServiceImpl implements ReportService {
    @Autowired
    private WorkspaceService workspaceService;

    @Autowired
    private LifecycleRecordMapper lifecycleRecordMapper;

    /**
     * 导出生命周期报表
     * @param requestDTO 入参DTO（可根据实际需求使用其中参数，比如日期范围等）
     * @param response   响应对象，用于输出Excel文件
     */
    @Override
    public void exportBusinessReport(ExportRequestDTO requestDTO, HttpServletResponse response) {
        // 处理可能为空的日期参数
        LocalDateTime startDateTime = null;
        LocalDateTime endDateTime = null;

        if (requestDTO.getStartDate() != null) {
            startDateTime = LocalDateTime.of(requestDTO.getStartDate(), LocalTime.MIN);
        }

        if (requestDTO.getEndDate() != null) {
            endDateTime = LocalDateTime.of(requestDTO.getEndDate(), LocalTime.MAX);
        }
        // 1. 查询数据库，获取多表联查后的生命周期报表数据列表
        // 这里假设你根据入参或默认逻辑调整日期范围，也可使用 requestDTO 里的日期参数
        List<LifecycleRecordVO> lifecycleRecordVOList = workspaceService.getLifecycleRecordList(
                requestDTO.getKeyword(), // 从DTO中获取keyword参数
                requestDTO.getOperationType(),
                startDateTime,  // 使用处理后的日期
                endDateTime     // 使用处理后的日期
        );

        // 2. 通过 POI 将数据写入 Excel 文件（基于模板）
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/生命周期管理报表.xlsx");
        try (XSSFWorkbook excel = new XSSFWorkbook(in);
             ServletOutputStream out = response.getOutputStream()) {

            XSSFSheet sheet = excel.getSheet("Sheet1");
            // 填充时间标题
            sheet.getRow(1).getCell(1).setCellValue("时间:" + requestDTO.getStartDate() + "至" + requestDTO.getEndDate());

            // 从第 4 行开始（假设模板第 4 行是数据起始行，可根据实际模板调整），遍历集合填充数据
            int rowIndex = 4;
            for (LifecycleRecordVO vo : lifecycleRecordVOList) {
                XSSFRow row = sheet.getRow(rowIndex);
                if (row == null) {
                    // 如果行不存在，创建新行
                    row = sheet.createRow(rowIndex);
                }
                row.getCell(1).setCellValue(vo.getId());
                row.getCell(2).setCellValue(vo.getSystemName());
                row.getCell(3).setCellValue(vo.getTableName());
                row.getCell(4).setCellValue(vo.getFieldName());
                row.getCell(5).setCellValue(vo.getOperation());
                row.getCell(6).setCellValue(vo.getOriginData());
                row.getCell(7).setCellValue(vo.getNewData());
                row.getCell(8).setCellValue(vo.getUserName());
                row.getCell(9).setCellValue(vo.getTime().toString());
                rowIndex++;
            }

            // 3. 输出 Excel 文件到客户端
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-Disposition", "attachment; filename=lifecycle_report.xlsx");
            excel.write(out);
        } catch (IOException e) {
            e.printStackTrace();
            // 可根据实际需求增加异常处理，比如返回错误提示给前端
        }
    }

    /**
     * 导入Excel文件
     *
     * @param file Excel文件
     * @return 导入结果
     */
    @Override
    public Result<Map<String, Object>> importExcel(MultipartFile file) {

        if (file == null || file.isEmpty()) {
            return Result.error("文件不能为空");
        }

        try {
            // 检查文件类型
            if (!isValidExcelFile(file)) {
                return Result.error("文件格式错误，请上传正确的Excel文件(.xlsx或.xls)");
            }

            List<LifecycleImportDTO> importList = readExcelFile(file);

            if (importList.isEmpty()) {
                return Result.error("Excel文件中没有有效数据");
            }

            // 数据校验和保存
            int successCount = 0;
            List<String> errorMessages = new ArrayList<>();

            for (int i = 0; i < importList.size(); i++) {
                LifecycleImportDTO dto = importList.get(i);
                try {
                    // 数据校验
                    if (!validateImportData(dto)) {
                        errorMessages.add("第" + (i + 2) + "行数据校验失败: 必填字段为空");
                        continue;
                    }

                    // 转换为VO对象并保存
                    LifecycleRecordVO recordVO = convertToVO(dto);

                    // 检查关联数据是否存在
                    Long sysId = lifecycleRecordMapper.getSysIdByNameDynamic(recordVO.getSystemName());
                    Long tableId = lifecycleRecordMapper.getTableIdByNameDynamic(recordVO.getTableName());
                    Long fieldId = lifecycleRecordMapper.getFieldIdByNameDynamic(recordVO.getFieldName());
                    
                    System.out.println("查询结果 - 系统ID: " + sysId + ", 表ID: " + tableId + ", 字段ID: " + fieldId);

                    if (sysId == null) {
                        errorMessages.add("第" + (i + 2) + "行数据保存失败: 系统'" + recordVO.getSystemName() + "'不存在");
                        continue;
                    }
                    if (tableId == null) {
                        errorMessages.add("第" + (i + 2) + "行数据保存失败: 表'" + recordVO.getTableName() + "'不存在");
                        continue;
                    }
                    if (fieldId == null) {
                        errorMessages.add("第" + (i + 2) + "行数据保存失败: 字段'" + recordVO.getFieldName() + "'不存在");
                        continue;
                    }

                    int result = lifecycleRecordMapper.insertLifecycleRecordDynamic(recordVO);
                    if (result <= 0) {
                        errorMessages.add("第" + (i + 2) + "行数据保存失败: 数据库插入返回0");
                    } else {
                        successCount++;
                    }
                } catch (Exception e) {
                    errorMessages.add("第" + (i + 2) + "行数据保存失败: " + e.getMessage());
                }
            }

            String message = "成功导入 " + successCount + " 条记录";
            if (!errorMessages.isEmpty()) {
                message += "，失败 " + errorMessages.size() + " 条记录";
            }
            // 检查是否全部失败
            if (successCount == 0 && !errorMessages.isEmpty()) {
                java.util.Map<String, Object> data = new java.util.HashMap<>();
                data.put("message", message);
                data.put("details", errorMessages);
                Result<java.util.Map<String, Object>> result = new Result<>();
                result.setCode(0);  // 设置 code = 0
                result.setMessage(message);
                result.setData(data);
                return result;
            }
            // 存在部分失败：在成功响应的 data 中返回 details 供前端展示
            if (!errorMessages.isEmpty()) {
                java.util.Map<String, Object> data = new java.util.HashMap<>();
                data.put("message", message);
                data.put("details", errorMessages);
                return Result.success(data);
            }

            // 全部成功，仅返回简要信息（依然放在 data 对象内，便于前端统一处理）
            java.util.Map<String, Object> data = new java.util.HashMap<>();
            data.put("message", message);
            return Result.success(data);
        } catch (Exception e) {
            // 让业务导入失败异常继续冒泡到全局异常处理（返回422）
            if (e instanceof com.isoft.exception.ImportFailException) {
                throw (com.isoft.exception.ImportFailException) e;
            }
            e.printStackTrace();
            return Result.error("导入失败: " + e.getMessage());
        }
    }

    /**
     * 读取Excel文件内容
     */
    private List<LifecycleImportDTO> readExcelFile(MultipartFile file) throws IOException {
        List<LifecycleImportDTO> dataList = new ArrayList<>();

        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = WorkbookFactory.create(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0);

            // 从第5行开始读取数据
            for (int i = 4; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                LifecycleImportDTO importData = new LifecycleImportDTO();

                // 读取各列数据（跳过第1列记录ID）
                // 从第2列开始读取：系统名、表名、字段名...
                Cell systemNameCell = row.getCell(1); // 系统名称（第2列）
                Cell tableNameCell = row.getCell(2);  // 表名（第3列）
                Cell fieldNameCell = row.getCell(3);  // 字段名（第4列）
                Cell operationCell = row.getCell(4);  // 操作类型（第5列）
                Cell originDataCell = row.getCell(5); // 原始数据（第6列）
                Cell newDataCell = row.getCell(6);    // 新数据（第7列）
                Cell userNameCell = row.getCell(7);   // 操作人（第8列）
                Cell timeCell = row.getCell(8);       // 操作时间（第9列）

                // 跳过整行空白（关键列均为空）
                boolean isKeyAllBlank = (systemNameCell == null || getCellValueAsString(systemNameCell).trim().isEmpty()) &&
                        (tableNameCell == null || getCellValueAsString(tableNameCell).trim().isEmpty()) &&
                        (fieldNameCell == null || getCellValueAsString(fieldNameCell).trim().isEmpty()) &&
                        (operationCell == null || getCellValueAsString(operationCell).trim().isEmpty());
                if (isKeyAllBlank) {
                    continue;
                }

                if (systemNameCell != null) {
                    String systemName = getCellValueAsString(systemNameCell);
                    System.out.println("第" + (i+1) + "行 - 系统名: [" + systemName + "]");
                    importData.setSystemName(systemName);
                }

                if (tableNameCell != null) {
                    String tableName = getCellValueAsString(tableNameCell);
                    System.out.println("第" + (i+1) + "行 - 表名: [" + tableName + "]");
                    importData.setTableName(tableName);
                }

                if (fieldNameCell != null) {
                    String fieldName = getCellValueAsString(fieldNameCell);
                    System.out.println("第" + (i+1) + "行 - 字段名: [" + fieldName + "]");
                    importData.setFieldName(fieldName);
                }

                if (operationCell != null) {
                    importData.setOperation(getCellValueAsString(operationCell));
                }

                if (originDataCell != null) {
                    importData.setOriginData(getCellValueAsString(originDataCell));
                }

                if (newDataCell != null) {
                    importData.setNewData(getCellValueAsString(newDataCell));
                }

                if (userNameCell != null) {
                    importData.setUserName(getCellValueAsString(userNameCell));
                }

                // 解析时间列（可选），支持 yyyy-MM-dd 或 yyyy-MM-dd HH:mm:ss；解析失败则忽略
                if (timeCell != null) {
                    String timeStr = getCellValueAsString(timeCell).trim();
                    if (!timeStr.isEmpty()) {
                        try {
                            if (timeStr.length() <= 10) {
                                importData.setTime(java.time.LocalDate.parse(timeStr).atStartOfDay());
                            } else {
                                importData.setTime(java.time.LocalDateTime.parse(timeStr.replace(' ', 'T')));
                            }
                        } catch (Exception ignored) {
                        }
                    }
                }

                dataList.add(importData);
            }
        } catch (Exception e) {
            throw new IOException("文件读取失败: " + e.getMessage(), e);
        }

        return dataList;
    }

    /**
     * 验证是否为有效的Excel文件
     */
    private boolean isValidExcelFile(MultipartFile file) {
        if (file == null || file.isEmpty()) {
            return false;
        }

        String contentType = file.getContentType();
        String fileName = file.getOriginalFilename();

        // 检查文件扩展名
        if (fileName != null) {
            fileName = fileName.toLowerCase();
            if (fileName.endsWith(".xlsx") || fileName.endsWith(".xls")) {
                // 进一步检查文件头
                try (InputStream inputStream = file.getInputStream()) {
                    byte[] header = new byte[8];
                    int bytesRead = inputStream.read(header);

                    if (bytesRead >= 8) {
                        // 检查Excel文件签名
                        String headerStr = bytesToHex(header);
                        // XLSX文件头: 504B0304
                        // XLS文件头: D0CF11E0A1B11AE1
                        return headerStr.startsWith("504B0304") ||
                               headerStr.startsWith("D0CF11E0A1B11AE1");
                    }
                } catch (IOException e) {
                    return false;
                }
            }
        }

        // 检查Content-Type
        if (contentType != null) {
            return contentType.equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") ||
                   contentType.equals("application/vnd.ms-excel") ||
                   contentType.equals("application/octet-stream");
        }

        return false;
    }

    /**
     * 将字节数组转换为十六进制字符串
     */
    private String bytesToHex(byte[] bytes) {
        StringBuilder sb = new StringBuilder();
        for (byte b : bytes) {
            sb.append(String.format("%02X", b));
        }
        return sb.toString();
    }

    /**
     * 获取单元格的字符串值
     * @param cell 单元格
     * @return 单元格的字符串值
     */
    private String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    /**
     * 数据校验
     */
    private boolean validateImportData(LifecycleImportDTO dto) {
        return dto.getSystemName() != null && !dto.getSystemName().trim().isEmpty() &&
               dto.getTableName() != null && !dto.getTableName().trim().isEmpty() &&
               dto.getFieldName() != null && !dto.getFieldName().trim().isEmpty() &&
               dto.getOperation() != null && !dto.getOperation().trim().isEmpty();
    }

    /**
     * 动态转换为VO对象
     */
    private LifecycleRecordVO convertToVO(LifecycleImportDTO dto) {
        LifecycleRecordVO vo = new LifecycleRecordVO();

        // 清洗数据，去除前后空格、制表符、单引号等特殊字符
        if (dto.getSystemName() != null) {
            String cleanSystemName = cleanString(dto.getSystemName());
            if (!cleanSystemName.isEmpty()) {
                vo.setSystemName(cleanSystemName);
            }
        }

        if (dto.getTableName() != null) {
            String cleanTableName = cleanString(dto.getTableName());
            if (!cleanTableName.isEmpty()) {
                vo.setTableName(cleanTableName);
            }
        }

        if (dto.getFieldName() != null) {
            String cleanFieldName = cleanString(dto.getFieldName());
            if (!cleanFieldName.isEmpty()) {
                vo.setFieldName(cleanFieldName);
            }
        }

        if (dto.getOperation() != null) {
            String cleanOperation = cleanString(dto.getOperation());
            if (!cleanOperation.isEmpty()) {
                vo.setOperation(cleanOperation);
            }
        }

        if (dto.getOriginData() != null) {
            vo.setOriginData(cleanString(dto.getOriginData()));
        }

        if (dto.getNewData() != null) {
            vo.setNewData(cleanString(dto.getNewData()));
        }

        if (dto.getUserName() != null) {
            String cleanUserName = cleanString(dto.getUserName());
            if (!cleanUserName.isEmpty()) {
                vo.setUserName(cleanUserName);
            }
        }

        if (dto.getTime() != null) {
            vo.setTime(dto.getTime());
        } else {
            // 若未提供时间，默认当前时间，避免因非空约束导致插入失败
            vo.setTime(java.time.LocalDateTime.now());
        }

        return vo;
    }

    /**
     * 清洗字符串，去除特殊字符
     * @param input 输入字符串
     * @return 清洗后的字符串
     */
    private String cleanString(String input) {
        if (input == null) {
            return "";
        }
        
        // 去除前后空格
        String cleaned = input.trim();
        
        // 去除单引号、双引号、制表符、换行符等特殊字符
        cleaned = cleaned.replaceAll("['\"\\t\\n\\r]", "");
        
        // 去除首尾的空白字符
        cleaned = cleaned.trim();
        
        return cleaned;
    }

}
